﻿CREATE TRIGGER tr_State_Insert_Pos
ON dbo.t_State
FOR INSERT,UPDATE
AS

IF NOT UPDATE(Pos) RETURN
DECLARE @Base sysname SET @Base = 'tr_State_'
DECLARE @LevelInsert varchar SET @LevelInsert = TRIGGER_NESTLEVEL(@@PROCID)
DECLARE @LevelDelete varchar SET @LevelDelete = TRIGGER_NESTLEVEL(Object_ID(@Base+'Delete_Pos'))

IF (@LevelInsert > 1) OR (@LevelDelete > 0)	RETURN

DECLARE @Pos int,@PosPrev int,@ID int,@ParentID int
SELECT @Pos = i.Pos, @ID = i.ID,
			 @ParentID = i.ParentID,@PosPrev = d.Pos
FROM inserted i LEFT OUTER JOIN deleted d ON i.ID = d.ID

IF @Pos > @PosPrev-- it's being moved down
	UPDATE t_State SET Pos = @Pos+2 FROM t_State WHERE ID = @ID

UPDATE t_State SET Pos = Pos + 1 WHERE ISNULL(ParentID,0) = ISNULL(@ParentID,0) AND Pos >= @Pos AND ID <> @ID

EXEC state_RePaginate @ParentID

